package com.idefav.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.RandomAccessFile;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.TempFile;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;


public class readxlsx {

	@Test
	public void read() throws Exception {
		String filename = this.getClass().getResource("/DataBaseDesign.xlsx").getPath();
		String template_path = this.getClass().getResource("/sqltemplate.txt").getPath();
		
		String template=txt2String(new File(template_path));
		
		InputStream inputStream = new FileInputStream(new File(filename));
		XSSFWorkbook workbook = new XSSFWorkbook(new File(filename));
		XSSFSheet sheet = workbook.getSheetAt(0);
		
		String tablename="";
		String tabledesc="";
		String col_template="[{name}] {type} NULL,";
		String col_str="";
		String col_desc_str="";
		String col_descTemplate="EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{desc}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{table}', @level2type=N'COLUMN',@level2name=N'{name}'\r\nGO";
		Iterator<Row> iterator=sheet.rowIterator();
		while (iterator.hasNext()) {
			Row row=iterator.next();
			Iterator<Cell> cells=row.cellIterator();
			int i=0;
			Cell cell1 = row.getCell(0);
			Cell cell2 = row.getCell(1);
			Cell cell3 = row.getCell(5);
			if(cell1.getStringCellValue().equals("表名")){
				// 保存文件
				String content= template.replace("{tablecol}", col_str).replace("{coldesc}", col_desc_str).replace("{table}", tablename).replace("{tabledesc}", tabledesc);
				col_str="";
				col_desc_str="";
				writeTxtFile(content, new File("sql/"+tablename+".sql"));
				tablename=cell2.getStringCellValue();
				tabledesc=cell3.getStringCellValue();
			}
			if(cell1.getStringCellValue().equals("列名")){
				continue;
			}
			
			String colName=cell1.getStringCellValue();
			String colType=cell2.getStringCellValue();
			String colDesc=cell3.getStringCellValue();
			
			String tmp_col=col_template.replace("{name}", colName).replace("{type}", colType);
			String tmp_desc_str=col_descTemplate.replace("{table}", tablename).replace("{name}", colName).replace("{desc}", colDesc);
			
			col_str+=tmp_col;
			col_desc_str+=tmp_desc_str;
			
		}
		
	}
	
	 public  boolean writeTxtFile(String content,File  fileName)throws Exception{  
		  RandomAccessFile mm=null;  
		  boolean flag=false;  
		  FileOutputStream o=null;  
		  try {  
		   o = new FileOutputStream(fileName);  
		      o.write(content.getBytes("GBK"));  
		      o.close();  
		//   mm=new RandomAccessFile(fileName,"rw");  
		//   mm.writeBytes(content);  
		   flag=true;  
		  } catch (Exception e) {  
		   // TODO: handle exception  
		   e.printStackTrace();  
		  }finally{  
		   if(mm!=null){  
		    mm.close();  
		   }  
		  }  
		  return flag;  
		 }  
	
	/**
     * 读取txt文件的内容
     * @param file 想要读取的文件对象
     * @return 返回文件内容
     */
    public static String txt2String(File file){
        StringBuilder result = new StringBuilder();
        try{
            BufferedReader br = new BufferedReader(new FileReader(file));//构造一个BufferedReader类来读取文件
            String s = null;
            while((s = br.readLine())!=null){//使用readLine方法，一次读一行
                result.append(System.lineSeparator()+s);
            }
            br.close();    
        }catch(Exception e){
            e.printStackTrace();
        }
        return result.toString();
    }
}
